package za.co.smartmi.dao.impl;

import za.co.smartmi.dao.SystemUserDao;
import za.co.smartmi.model.SystemUser;
import za.co.smartmi.model.SystemUserPk;
import za.co.smartmi.dao.exceptions.SystemUserDaoException;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import za.co.smartmi.dao.support.JdbcGenericDao;

@Repository
public class SystemUserDaoImpl extends JdbcGenericDao implements ParameterizedRowMapper<SystemUser>, SystemUserDao
{
    
        @Autowired
        public void setDataSource(DataSource dataSource) {
                setJdbcTemplate(new JdbcTemplate(dataSource));
	}
        
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return SystemUserPk
	 */
	@Transactional
	public SystemUserPk insert(SystemUser dto)
	{
		getJdbcTemplate().update("INSERT INTO " + getTableName() + " ( username, password, status, startdate, lastlogindate ) VALUES ( ?, ?, ?, ?, ? )",dto.getUsername(),dto.getPassword(),dto.getStatus(),dto.getStartdate(),dto.getLastlogindate());
		return dto.createPk();
	}

	/** 
	 * Updates a single row in the system_user table.
	 */
	@Transactional
	public void update(SystemUserPk pk, SystemUser dto) throws SystemUserDaoException
	{
		getJdbcTemplate().update("UPDATE " + getTableName() + " SET username = ?, password = ?, status = ?, startdate = ?, lastlogindate = ? WHERE username = ?",dto.getUsername(),dto.getPassword(),dto.getStatus(),dto.getStartdate(),dto.getLastlogindate(),pk.getUsername());
	}

	/** 
	 * Deletes a single row in the system_user table.
	 */
	@Transactional
	public void delete(SystemUserPk pk) throws SystemUserDaoException
	{
		getJdbcTemplate().update("DELETE FROM " + getTableName() + " WHERE username = ?",pk.getUsername());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return SystemUser
	 */
	public SystemUser mapRow(ResultSet rs, int row) throws SQLException
	{
		SystemUser dto = new SystemUser();
		dto.setUsername( rs.getString( 1 ) );
		dto.setPassword( rs.getString( 2 ) );
		dto.setStatus( rs.getString( 3 ) );
		dto.setStartdate( rs.getDate(4 ) );
		dto.setLastlogindate( rs.getDate(5 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "smartMI.system_user";
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'username = :username'.
	 */
	@Transactional
	public SystemUser findByPrimaryKey(String username) throws SystemUserDaoException
	{
		try {
			List<SystemUser> list = getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE username = ?", this,username);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria ''.
	 */
	@Transactional
	public List<SystemUser> findAll() throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " ORDER BY username", this);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'username = :username'.
	 */
	@Transactional
	public List<SystemUser> findWhereUsernameEquals(String username) throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE username = ? ORDER BY username", this,username);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'password = :password'.
	 */
	@Transactional
	public List<SystemUser> findWherePasswordEquals(String password) throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE password = ? ORDER BY password", this,password);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'status = :status'.
	 */
	@Transactional
	public List<SystemUser> findWhereStatusEquals(String status) throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE status = ? ORDER BY status", this,status);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'startdate = :startdate'.
	 */
	@Transactional
	public List<SystemUser> findWhereStartdateEquals(Date startdate) throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE startdate = ? ORDER BY startdate", this,startdate);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user table that match the criteria 'lastlogindate = :lastlogindate'.
	 */
	@Transactional
	public List<SystemUser> findWhereLastlogindateEquals(Date lastlogindate) throws SystemUserDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, password, status, startdate, lastlogindate FROM " + getTableName() + " WHERE lastlogindate = ? ORDER BY lastlogindate", this,lastlogindate);
		}
		catch (Exception e) {
			throw new SystemUserDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the system_user table that matches the specified primary-key value.
	 */
	public SystemUser findByPrimaryKey(SystemUserPk pk) throws SystemUserDaoException
	{
		return findByPrimaryKey( pk.getUsername() );
	}

}
